package app1.rapidsquare.com.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * Created by Hikmat on 14.08.2014.
 */
public class AgileSqliteHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "agilenyc.db";
    private static final int DATABASE_VERSION = 1;

    /*private final String CONTACTS_SQL_QUERY = " CREATE TABLE ["
            + AgileContactsProvider.TABLE_NAME
            + "] ("
            + " [Id] INT NOT NULL ON CONFLICT REPLACE CONSTRAINT [pk] UNIQUE ON CONFLICT ABORT COLLATE RTRIM DEFAULT 0,"
            + " [SentUserId] INT NOT NULL COLLATE RTRIM,"
            + " [ConfirmUserId] INT NOT NULL COLLATE RTRIM,"
            + " [ConfirmDate] DATETIME COLLATE RTRIM,"
            + " [Note] NVARCHAR(4000) COLLATE RTRIM,"
            + " [NotificationStatus] BOOL,"
            + " [SentDate] DATETIME COLLATE RTRIM,"
            + " [ModifiedDate] DATETIME NOT NULL,"
            + " [IsDelete] BOOL NOT NULL);"
            + " CREATE UNIQUE INDEX [sent_user_index] ON [AgileContactsProvider.TABLE_NAME] ([SentUserId] COLLATE RTRIM ASC);";*/


    private final String CONTACTS_SQL_QUERY = " CREATE TABLE ["
            + AgileContactsProvider.TABLE_NAME
            + "] ("
            + " [Id] INT NOT NULL ON CONFLICT REPLACE CONSTRAINT [pk] UNIQUE ON CONFLICT ABORT COLLATE RTRIM DEFAULT 0,"
            + " [Email] NVARCHAR(150) NOT NULL COLLATE RTRIM,"
            + " [FirstName] NVARCHAR(50) COLLATE RTRIM,"
            + " [LastName] NVARCHAR(50) COLLATE RTRIM,"
            + " [Phone] NVARCHAR(100) COLLATE RTRIM,"
            + " [Title] NVARCHAR(500) COLLATE RTRIM,"
            + " [Company] NVARCHAR(250) COLLATE RTRIM,"
            + " [Site] NVARCHAR(150) COLLATE RTRIM,"
            + " [Info] NVARCHAR(500) COLLATE RTRIM,"
            + " [Code] INT NOT NULL ON CONFLICT REPLACE COLLATE RTRIM,"
            + " [AdditionalInfo] NVARCHAR(1000) COLLATE RTRIM,"
            + " [ModifiedDate] DATETIME NOT NULL COLLATE RTRIM,"
            + " [City] NVARCHAR(150) COLLATE RTRIM,"
            + " [State] NVARCHAR(150) COLLATE RTRIM,"
            + " [FacebookId] NVARCHAR(500) COLLATE RTRIM,"
            + " [GooglePlusId] NVARCHAR(500) COLLATE RTRIM,"
            + " [LinkedInId] NVARCHAR(500) COLLATE RTRIM,"
            + " [FacebookName] NVARCHAR(150) COLLATE RTRIM,"
            + " [GooglePlusName] NVARCHAR(150) COLLATE RTRIM,"
            + " [LinkedInName] NVARCHAR(150) COLLATE RTRIM,"
            + " [Note] NVARCHAR(1000) COLLATE RTRIM);";

    private final String EVETNS_SQL_QUERY = " CREATE TABLE ["
            + AgileEventsProvider.TABLE_NAME
            + "] ("
            + " [Id] INT NOT NULL ON CONFLICT REPLACE CONSTRAINT [pk_key] UNIQUE ON CONFLICT ABORT COLLATE RTRIM,"
            + " [Topic] NVARCHAR(500) NOT NULL COLLATE RTRIM,"
            + " [Introduction] NVARCHAR(4000) NOT NULL COLLATE RTRIM,"
            + " [Author] NVARCHAR(250) NOT NULL COLLATE RTRIM,"
            + " [Room] NVARCHAR(2) NOT NULL COLLATE RTRIM,"
            + " [StartTime] DATETIME NOT NULL COLLATE RTRIM,"
            + " [EndTime] DATETIME NOT NULL COLLATE RTRIM,"
            + " [AuthorImageUrl] NVARCHAR(1000),"
            + " [ModifiedDate] DATETIME NOT NULL COLLATE RTRIM,"
            + " [IsDelete] BOOL NOT NULL,"
            + " [IsCheckIn] BOOL NOT NULL,"
            + " [UserRating] FLOAT NOT NULL,"
            + " [AvgRating] FLOAT NOT NULL);";


    private final String USERS_SQL_QUERY = "CREATE TABLE ["
            + AgileUserProvider.TABLE_NAME
            + "] ("
            + " [Id] INT NOT NULL ON CONFLICT REPLACE CONSTRAINT [pk] UNIQUE ON CONFLICT ABORT COLLATE RTRIM,"
            + " [Email] NVARCHAR(150) NOT NULL COLLATE RTRIM,"
            + " [Password] NVARCHAR(250) NULL COLLATE RTRIM,"
            + " [FirstName] NVARCHAR(50) COLLATE RTRIM,"
            + " [LastName] NVARCHAR(50) COLLATE RTRIM,"
            + " [Phone] NVARCHAR(100) COLLATE RTRIM,"
            + " [Title] NVARCHAR(500) COLLATE RTRIM,"
            + " [Company] NVARCHAR(250) COLLATE RTRIM,"
            + " [Site] NVARCHAR(150) COLLATE RTRIM,"
            + " [Info] NVARCHAR(500) COLLATE RTRIM,"
            + " [Code] INT NOT NULL ON CONFLICT REPLACE COLLATE RTRIM,"
            + " [AdditionalInfo] NVARCHAR(1000) COLLATE RTRIM,"
            + " [ModifiedDate] DATETIME NOT NULL COLLATE RTRIM,"
            + " [City] NVARCHAR(150) COLLATE RTRIM,"
            + " [State] NVARCHAR(150) COLLATE RTRIM,"
            + " [FacebookId] NVARCHAR(500) COLLATE RTRIM,"
            + " [GooglePlusId] NVARCHAR(500) COLLATE RTRIM,"
            + " [LinkedInId] NVARCHAR(500) COLLATE RTRIM,"
            + " [FacebookName] NVARCHAR(150) COLLATE RTRIM,"
            + " [GooglePlusName] NVARCHAR(150) COLLATE RTRIM,"
            + " [LinkedInName] NVARCHAR(150) COLLATE RTRIM,"
            + " [IsDelete] BOOL NOT NULL);";


    public AgileSqliteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        sqLiteDatabase.execSQL(CONTACTS_SQL_QUERY);
        sqLiteDatabase.execSQL(EVETNS_SQL_QUERY);
        sqLiteDatabase.execSQL(USERS_SQL_QUERY);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        if (oldVersion >= newVersion)
            return;

        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + AgileContactsProvider.TABLE_NAME);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + AgileEventsProvider.TABLE_NAME);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + AgileUserProvider.TABLE_NAME);
        onCreate(sqLiteDatabase);
    }

    @Override
    public synchronized void close() {
        super.close();
    }
}
